<?php
ini_set('display_errors', 'On');
$db = "w4111c.cs.columbia.edu:1521/adb";
$conn = oci_connect("jcz2105", "goodweather", $db);

if (isset($_POST['create'])) {
	$query = "INSERT INTO ".convert_field_to_db_field($db_name)." VALUES (";
	foreach ($fields as $field)
            $query .= "'".$_POST[convert_field_to_db_field($field)]."',";
	$query = substr($query, '', -1); //removes last comma
	$query = $query.")";

	$create_stmt = oci_parse($conn, $query);

	oci_execute($create_stmt, OCI_DEFAULT);

	oci_commit($conn);
} elseif (isset($_POST['delete'])) {
	$array = array();
	$array = $_POST['checkbox'];

        foreach ($array as $cb) {
                $query = "DELETE FROM ".$db_name." WHERE ".convert_field_to_db_field($fields[0])."='".$cb."'";
                $delete_stmt = oci_parse($conn, $query);
                oci_execute($delete_stmt, OCI_DEFAULT);
                oci_commit($conn);
        }
}

if (isset($_POST['filter'])) {
    $filter_params = array();

    foreach ($fields as $field) {
        if ($_POST[convert_field_to_db_field($field)] != "")
            $filter_params[$field] = process_user_input($_POST[convert_field_to_db_field($field)]);
    }

    $query_filters = "";
    if (count($filter_params) > 0) {
        $query_filters .= " where";
        foreach ($filter_params as $field => $param) {
            $query_filters .= " " . convert_field_to_db_field($field) . " = '" . $param . "' AND";
        }
        $query_filters .= " 1 = 1"; // Handles trailing 'AND'
    }

    $query = "select * from ".convert_field_to_db_field($db_name) . $query_filters;
    $stmt = oci_parse($conn, $query);
} else {
    // If no filters, query entire table
    $stmt = oci_parse($conn, "select * from ".convert_field_to_db_field($db_name));
}
oci_execute($stmt, OCI_DEFAULT);

//START Visual Elements
echo "<a href='index.php'>Return to Main Menu</a><p style='font-size:18px'><i><b> ".$db_name." Table </b></i></p>";

//START Display main query
echo "<form method='post'>";
echo "<table border='1'>
<tr>";
echo "<th></th>";
foreach($fields as $field)
    echo "<th>".$field."</th>";
echo "</tr>";

while ($res = oci_fetch_row($stmt))
{
	echo "<tr>";
	echo "<td><input name='checkbox[]' type='checkbox' id='checkbox[]' value='".$res[0]."'></td>";
	//echo "<td>".$res[0]."</td>";
	for ($counter = 0; $counter < count($fields); $counter++)
            echo "<td>".$res[$counter]."</td>" ;
	echo "</tr>";
}
echo "</table>";
echo "<input name='delete' type='submit' value='Delete Selected'>";
echo "</form>";
//END Display main query

//START filter main query
echo "<p><b>Filter search results</b>";
echo "<form method='post'>";

foreach($fields as $field)
    //echo "<input type='text' name='clientid'/>";
    echo $field." <input type=\"text\" name=\"".convert_field_to_db_field($field)."\"/>";

echo "<input name='filter' type='submit' value='Filter'>";
echo "</input></form></P>";
//END filter main query


//START query form with text input
echo "<p><b>Predefined search</b>";
echo "
<form method='get' action='result.php'>
<select name='t1'>

<option value=q1 >Search for Jewelry made with stone (ItemID)</option>
<option value=q2 >Search for Jewelry made with wire (ItemID)</option>
<option value=q3 >Search price and supplier for inventory (Item ID)</option>
<option value=q4 >Search all orders for client (Cient ID)</option>
<option value=q5 >Search most popular jewelry (no additional entry)</option>
<option value=q7 >Search projects where quantity produced is less than (number)</option>
<option value=q8 >Search jewelry projects that has not been ordered (no additional entry)</option>
<option value=q9 >Search jewlery projects where all parts are supplied by the same supplier(no additional entry)</option>
<input type='text'  name='searchvalue' />
</select>

<input type=submit value=Submit>
</form>";
//END query form with text input

//<option value=q6 >find client orders since March 1, 2010 (Client ID or none for all clients)</option>




//START Create new entry
echo "<P>";
echo "<br><b>Create new ".$db_name.":</b></br>";
echo "<form method='post'>";
foreach($fields as $field)
    echo $field." <input type=\"text\" name=\"".convert_field_to_db_field($field)."\"/>";
echo "<input name='create' type='submit' value='Create'>";
echo "</input></form></P>";
//END Create new entry

//END Visual Elements

function convert_field_to_db_field($field) {
	return strtolower(str_replace(" ", "", $field));
}

function process_user_input($input) {
    $value = htmlspecialchars(trim($input));
    if (get_magic_quotes_gpc()) 
        $input = stripslashes($input);
    return $input;
}

//register_shutdown_function('errorHandler');
//function errorHandler() {
   //$err = error_get_last();
   //if($err)
     //include "error_page.php"; // your custom error page
//}

oci_close($conn);

?>
